SET NOCOUNT ON;
declare @release smallint = 7;
declare @version smallint = 0;
declare @script_name varchar(256);
declare @script_type varchar(20)='CensusData';
set @script_name=@script_type+'_V'+convert(varchar(5),@version)+'R'+right('00000'+convert(varchar(5),@release),5)+'.sql'

if exists (select 1 from dbo.database_version_information where script_name=@script_name and undone=0)
	print 'script '+@script_name+' already executed, please undo then re-execute.';
else
begin try
begin tran
/*--Begin Code Here --*/

update ACSSF.Geography_File
set FILETYPE='2013e1';

ALTER TABLE [ACSSF].[Geography_File] DROP CONSTRAINT [ACSSF__Geography_File_PK]

ALTER TABLE [ACSSF].[Geography_File] ADD  CONSTRAINT [UN_ACSSF__Geography_File] UNIQUE CLUSTERED 
(
	[FILEID] ASC,
	[STUSAB] ASC,
	[LOGRECNO] ASC,
	FILETYPE ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]



if not exists (select 1 from sys.schemas where name='Congressional_District')
	execute('create schema Congressional_District authorization dbo');

if exists (select * from sys.views where object_id=object_id('Congressional_District.Unweighted_Sample_Count_Of_The_Population'))
	drop view Congressional_District.Unweighted_Sample_Count_Of_The_Population;

if exists (select * from sys.views where object_id=object_id('Congressional_District.Unweighted_Sample_Housing_Units'))
	drop view Congressional_District.Unweighted_Sample_Housing_Units;

declare @tsql varchar(max);
set @tsql='
create view Congressional_District.Unweighted_Sample_Count_Of_The_Population
as
select
convert(varchar(256),ltrim(rtrim(gf.Name))) as [Congressional_District_Description]
,gf.STUSAB as [State_Abbreviation]
,gf.STATE as [FIPS_State_Code]
,convert(smallint,gf.SLDL) as [State_Congressional_District_Number]
,sf1.FileType as [Sample_Set]
,sf1.Unweighted_Sample_Count_Of_The_Population
,gf.GEOID as [GeoId]
from
ACSSF.Summary_File_0001 sf1
inner join
ACSSF.Geography_File gf
on
gf.FILEID=sf1.FILEID
and
gf.STUSAB=sf1.STUSAB
and
gf.LOGRECNO=sf1.LOGRECNO
and
gf.FILETYPE=sf1.FILETYPE
where
gf.SUMLEVEL=''500''
and
gf.COMPONENT=''00''
';
execute (@tsql);

set @tsql='
create view Congressional_District.Unweighted_Sample_Housing_Units
as
select
convert(varchar(256),ltrim(rtrim(gf.Name))) as [Congressional_District_Description]
,gf.STUSAB as [State_Abbreviation]
,gf.STATE as [FIPS_State_Code]
,convert(smallint,gf.SLDL) as [State_Congressional_District_Number]
,sf1.FileType as [Sample_Set]
,sf1.Unweighted_Sample_Housing_Units
,gf.GEOID as [GeoId]
from
ACSSF.Summary_File_0001 sf1
inner join
ACSSF.Geography_File gf
on
gf.FILEID=sf1.FILEID
and
gf.STUSAB=sf1.STUSAB
and
gf.LOGRECNO=sf1.LOGRECNO
and
gf.FILETYPE=sf1.FILETYPE
where
gf.SUMLEVEL=''500''
and
gf.COMPONENT=''00''
';
execute (@tsql);

/*--End Code Here --*/
if exists (select 1 from dbo.database_version_information where script_name=@script_name and undone=1)
	update dbo.database_version_information 
	set undone=0
	,date_released=getdate()
	where
	script_name=@script_name;
else
	insert into dbo.database_version_information 
	values(@script_name,@script_type,@version,@release,GETDATE(),0);
	
print 'script '+@script_name+' succeeded!'
commit tran
end try
begin catch 
	declare @error_message varchar(2000);
	set @error_message='SCRIPT:'+@script_name+' FAILED! ROLLBACK!  ERROR_MESSAGE:'
	+coalesce(error_message(),'')+' ERROR_NUMBER:'+coalesce(convert(varchar(10),error_number()),'');
	ROLLBACK
	raiserror(@error_message,16,1)
end catch
